﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;

namespace DAO
{
    public class NhaCungCapDAO
    {
        //----------------------KIM-----------------------
        public List<NhaCungCapDTO> KLayDSNCC()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sql = "SELECT MaNhaCungCap, TenNhaCungCap FROM NHACUNGCAP";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataReader reader = cmd.ExecuteReader();
            List<NhaCungCapDTO> ds = new List<NhaCungCapDTO>();

            while (reader.Read())
            {
                NhaCungCapDTO nccDTO = new NhaCungCapDTO();

                nccDTO.MaNhaCungCap = reader.GetInt32(0);
                nccDTO.TenNhaCungCap = reader.GetString(1);


                ds.Add(nccDTO);
            }

            conn.Close();
            return ds;

        }
        //END---------------------KIM----------------

        //----------KHANG----------
        public string KetNoi()
        {
            string kq = "";
            SqlConnection conn = DataProvider.ConnectDB();
            if (conn == null)
                kq = "That bai";
            else
                kq = "Thanh cong";
            conn.Close();
            return kq;

        }
        public List<NhaCungCapDTO> GetNhaCungCapAll()
        {
            List<NhaCungCapDTO> danhsach = new List<NhaCungCapDTO>();
            string sql = "SELECT * FROM NHACUNGCAP";

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                NhaCungCapDTO nccDTO = new NhaCungCapDTO();
                nccDTO.MaNhaCungCap = reader.GetInt32(0);
                nccDTO.TenNhaCungCap = reader.GetString(1);
                nccDTO.SoDienThoai = reader.GetString(2);
                nccDTO.Email = reader.GetString(3);
                nccDTO.DiaChi = reader.GetString(4);
                nccDTO.Website = reader.GetString(5);
                nccDTO.Fax = reader.GetString(6);
                nccDTO.TaiKhoanNganHang = reader.GetString(7);
                danhsach.Add(nccDTO);
            }
            conn.Close();
            return danhsach;
        }

        //----------END KHANG----------
        //-------------HAI-----------
        public List<NhaCungCapDTO> LayDanhSachNhaCungCap()
        {
            List<NhaCungCapDTO> ListNCC = new List<NhaCungCapDTO>();
            string sql = "Select *from NHACUNGCAP";
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                NhaCungCapDTO nccDTO = new NhaCungCapDTO();
                nccDTO.MaNhaCungCap = reader.GetInt32(0);
                nccDTO.TenNhaCungCap = reader.GetString(1);
                nccDTO.SoDienThoai = reader.GetString(2);
                nccDTO.Email = reader.GetString(3);
                nccDTO.DiaChi = reader.GetString(4);
                nccDTO.Fax = reader.GetString(5);
                nccDTO.Website = reader.GetString(6);
                nccDTO.TaiKhoanNganHang = reader.GetString(7);


                ListNCC.Add(nccDTO);


            }
            conn.Close();
            return ListNCC;

        }
        public string ThemNhaCungCap(NhaCungCapDTO nccDTO)
        {
            string mess = "";
            string sql = "Insert into";
            sql += "NHACUNGCAP(MaNhaCungCap,TenNhaCungCap,DienThoai,Email,DiaChi,Fax,Website,TaiKhoanNganHang)";
            sql += "values(@MaNhaCungCap,@TenNhaCungCap,@DienThoai,@Email,@DiaChi,@Fax,@Website,@TaiKhoanNganHang)";
            SqlConnection conn = DataProvider.ConnectDB();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlParameter para;
                para = cmd.Parameters.Add("@MaNhaCungCap", SqlDbType.Int);
                para.Value = nccDTO.MaNhaCungCap;
                para = cmd.Parameters.Add("@TenNhaCungCap", SqlDbType.VarChar);
                para.Value = nccDTO.TenNhaCungCap;
                para = cmd.Parameters.Add("@DienThoai", SqlDbType.VarChar);
                para.Value = nccDTO.SoDienThoai;
                para = cmd.Parameters.Add("@Email", SqlDbType.VarChar);
                para.Value = nccDTO.Email;
                para = cmd.Parameters.Add("@DiaChi", SqlDbType.VarChar);
                para.Value = nccDTO.DiaChi;
                para = cmd.Parameters.Add("@Fax", SqlDbType.VarChar);
                para.Value = nccDTO.Fax;
                para = cmd.Parameters.Add("@Website", SqlDbType.VarChar);
                para.Value = nccDTO.Website;
                para = cmd.Parameters.Add("@TaiKhoanNganHang", SqlDbType.VarChar);
                para.Value = nccDTO.TaiKhoanNganHang;
                cmd.ExecuteNonQuery();
                conn.Close();


            }
            catch (SqlException ex)
            {
                mess = ex.Message;
            }
            finally
            {
                conn.Close();

            }
            return mess;
        }
        public int LayMaNhaCungCap()
        {
            int max = 0;
            List<NhaCungCapDTO> ListNCC = LayDanhSachNhaCungCap();
            if (ListNCC.Count < 1)
            {
                return max;
            }
            for (int i = 0; i < ListNCC.Count; i++)
            {
                if (ListNCC[i].MaNhaCungCap > max)
                {
                    max = ListNCC[i].MaNhaCungCap;
                }
            }
            return max;
        }
        public int KiemTraTonTaiNhaCungCap(string tenNCC, string sdt, string diachi)
        {

            int ma = 0;
            string sql = "Select MaNhaCungCap From NHACUNGCAP where TenNhaCungCap like N'" + tenNCC + "' and DienThoai like '" + sdt + "'";
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
                ma = reader.GetInt32(0);
            conn.Close();
            string sql2 = "Select MaNhaCungCap From NHACUNGCAP where TenNhaCungCap like N'" + tenNCC + "' and DiaChi like N'" + diachi + "'";
            SqlConnection conn2 = DataProvider.ConnectDB();
            SqlCommand cmd2 = new SqlCommand(sql2, conn2);
            SqlDataReader reader2 = cmd2.ExecuteReader();
            while (reader2.Read())
                ma = reader2.GetInt32(0);
            conn2.Close();
            return ma;
        }
        public string XoaNhaCungCap(int maNCC)
        {
            string mess = "";
            string sql = "Delete * From NHACUNGCAP where MaNhaCungCap = " + maNCC;
            SqlConnection conn = DataProvider.ConnectDB();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                mess = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return mess;
        }
        public NhaCungCapDTO LayTenThongTinNhaCungCapTheoMa(int maNCC)
        {

            string sql = "Select TenNhaCungCap,DienThoai,Email,DiaChi,Fax,Website,TaiKhoanNganHang From NHACUNGCAP Where MaNhaCungCap= " + maNCC;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            NhaCungCapDTO nccDTO = new NhaCungCapDTO();
            while (reader.Read())
            {

                nccDTO.MaNhaCungCap = reader.GetInt32(0);
                nccDTO.TenNhaCungCap = reader.GetString(1);
                nccDTO.SoDienThoai = reader.GetString(2);
                nccDTO.Email = reader.GetString(3);
                nccDTO.TaiKhoanNganHang = reader.GetString(4);
                nccDTO.DiaChi = reader.GetString(5);
                nccDTO.Website = reader.GetString(6);
                nccDTO.Fax = reader.GetString(7);

            }
            conn.Close();
            return nccDTO;
        }
        DataProvider dp = new DataProvider();
        public DataTable DataNhaCungCap()
        {
            return dp.GetData("select * from NhaCungCap");
        }
        public void insertncc(NhaCungCapDTO nccDTO)
        {
            dp.ExeCuteNonQuery(@"insert into NHACUNGCAP(MaNhaCungCap,TenNhaCungCap,DienThoai,Email,DiaChi,Fax,Website,TaiKhoanNganHang) values(" + nccDTO.MaNhaCungCap + ", N'" + nccDTO.TenNhaCungCap + "','" + nccDTO.SoDienThoai +
                "','" + nccDTO.Email + "','" + nccDTO.DiaChi + "','" + nccDTO.Fax + "','" + nccDTO.Website + "','" + nccDTO.TaiKhoanNganHang + "')");
        }
        public void deletencc(int mncc)
        {
            dp.ExeCuteNonQuery(@"delete From NHACUNGCAP where MaNhaCungCap =" + mncc);
        }
        public void updatencc(NhaCungCapDTO nccDTO)
        {
            dp.ExeCuteNonQuery("update NHACUNGCAP set TenNhaCungCap = N'" + nccDTO.TenNhaCungCap + "',DienThoai ='" +
                nccDTO.SoDienThoai + "',Email = '" + nccDTO.Email + "',DiaChi = N'" + nccDTO.DiaChi + "',Fax = '" + nccDTO.Fax + "',Website = '" + nccDTO.Website + "',TaiKhoanNganHang = '" + nccDTO.TaiKhoanNganHang + "' where MaNhaCungCap = " + nccDTO.MaNhaCungCap);

        }
       //----------------END HAI-------------------------
    }
}
